'use strict';

const BaseModel = require('./BaseModel');

/**
 *  表信息
 */

class ActiveModel extends BaseModel {
    /*
     *统计
     */
    async getH5ActiveList(app, params) {
        let pageIndex = parseInt(params.pageIndex) || 0;
        const pageSize = parseInt(params.pageSize) || 10;
        if (pageIndex - 1 <= 0) {
            pageIndex = 1;
        }
        let sql = 'select  u.real_name,h5.idcard_num,h5.union_id,h5.amount, h5.create_time  from h5_red_record h5,wx_pg_user   u  where u.idcard_num = h5.idcard_num ';
        if (params.idCardNum) {
            sql += ` and h5.idcard_num = '${params.idCardNum}' `;
        }
        if (params.realName) {
            sql += ` and u.real_name like '%${params.realName}%' `;
        }
        if (params.createTime) {
            sql += ` and h5.create_time BETWEEN '${params.createTime[0]}' and '${params.createTime[1]}' `;
        }
        let countSql = `select count(*) count from (${sql}) t`;
        const totalCountRes = await app.mysql.query(countSql);
        sql += ` order by h5.create_time desc limit ${(pageIndex - 1) * pageSize}, ${pageSize} `;
        const result = await app.mysql.query(sql);
        const totalCount = totalCountRes[0].count;
        const totalPage = Math.ceil(totalCount / pageSize);
        const entityList = [];
        if (result.length) {
            for (let o of result) {
                let res = {};
                for (let key in o) {
                    let keyName = key.replace(/\_[a-z]/g, val => val.toUpperCase().replace(/\_/g, ''));
                    res[keyName] = o[key];
                }
                entityList.push(res);
            }
        }
        return {
            result: entityList,
            pageSize,
            pageIndex,
            totalCount,
            totalPage
        };
    }

    async getH5ActiveCaculate(app, params) {
        let pageIndex = parseInt(params.pageIndex) || 0;
        const pageSize = parseInt(params.pageSize) || 10;
        if (pageIndex - 1 <= 0) {
            pageIndex = 1;
        }
        let sql = 'select  u.real_name,c.create_time banlitime,h5.idcard_num,h5.union_id,h5.amount, h5.create_time  from h5_red_record h5,wx_pg_user   u,ais_contract c  where u.idcard_num = h5.idcard_num  and c.signer_credentials_no = h5.idcard_num and c.contract_mould_id = h5.contract_mould_id ';
        if (params.idCardNum) {
            sql += ` and h5.idcard_num = '${params.idCardNum}' `;
        }
        if (params.realName) {
            sql += ` and u.real_name like '%${params.realName}%' `;
        }
        if (params.createTime) {
            sql += ` and h5.create_time BETWEEN '${params.createTime[0]}' and '${params.createTime[1]}' `;
        }
        if (params.banlitime) {
            sql += ` and c.create_time BETWEEN '${params.banlitime[0]}' and '${params.banlitime[1]}' `;
        }
        let groupSql = `select b.real_name,b.banlitime,sum(b.amount) totalRed,count(b.real_name) cishu,b.idcard_num from (${sql}) b group by b.real_name `;
        let countSql = `select count(*) count from (${groupSql}) t`;
        const totalCountRes = await app.mysql.query(countSql);
        groupSql += ` order by b.banlitime desc limit ${(pageIndex - 1) * pageSize}, ${pageSize} `;
        app.logger.debug('SQL:', groupSql);
        const result = await app.mysql.query(groupSql);
        const totalCount = totalCountRes[0].count;
        const totalPage = Math.ceil(totalCount / pageSize);
        const entityList = [];
        if (result.length) {
            for (let o of result) {
                let res = {};
                for (let key in o) {
                    let keyName = key.replace(/\_[a-z]/g, val => val.toUpperCase().replace(/\_/g, ''));
                    res[keyName] = o[key];
                }
                entityList.push(res);
            }
        }
        return {
            result: entityList,
            pageSize,
            pageIndex,
            totalCount,
            totalPage
        };
    }
}

module.exports = ActiveModel;
